﻿using Model;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Utility.Helper;

namespace BLL
{
    public class QS_PLANMonth_DEPARTMENTBLL : BaseBLL
    {
        #region 实体操作

        public QS_PLANMonth_DEPARTMENT Find(int id)
        {
            var sql = "SELECT * FROM QS_PLANMonth_DEPARTMENT T1 WHERE T1.N_ID = :N_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":N_ID", id, "int");
            var dt = Query(sql, parms);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            var entity = new QS_PLANMonth_DEPARTMENT();
            entity.N_ID = dt.Rows[0].GetValue<int>("N_ID");
            entity.N_PROJECT_ID = dt.Rows[0].GetValue<int>("N_PROJECT_ID");
            entity.C_PROJECT_NAME = dt.Rows[0].GetValue<string>("C_PROJECT_NAME");
            entity.N_PARENT_PROJECT_ID = dt.Rows[0].GetValue<int>("N_PARENT_PROJECT_ID");
            entity.C_PARENT_PROJECT_NAME = dt.Rows[0].GetValue<string>("C_PARENT_PROJECT_NAME");
            entity.N_TYPE = dt.Rows[0].GetValue<int>("N_TYPE");
            entity.N_MONEY = dt.Rows[0].GetValue<decimal?>("N_MONEY");
            entity.N_APPLY_ID = dt.Rows[0].GetValue<int>("N_APPLY_ID");
            entity.N_USER_ID = dt.Rows[0].GetValue<int>("N_USER_ID");
            entity.C_REMARK = dt.Rows[0].GetValue<string>("C_REMARK");
            return entity;
        }

        public bool Insert(QS_PLANMonth_DEPARTMENT entity)
        {
            entity.N_ID = (int)GetSequence("SEQ_MONTHDEPARTMENT_ID");
            var sql = @"INSERT INTO QS_PLANMonth_DEPARTMENT(
	N_ID,
	N_PROJECT_ID,
	C_PROJECT_NAME,
	N_PARENT_PROJECT_ID,
	C_PARENT_PROJECT_NAME,
	N_TYPE,
	N_MONEY,
	N_APPLY_ID,
	N_USER_ID,
    C_REMARK,
    N_APPLY_TYPE)
VALUES(
	:N_ID,
	:N_PROJECT_ID,
	:C_PROJECT_NAME,
	:N_PARENT_PROJECT_ID,
	:C_PARENT_PROJECT_NAME,
	:N_TYPE,
	:N_MONEY,
	:N_APPLY_ID,
	:N_USER_ID,
    :C_REMARK,
    :N_APPLY_TYPE)";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":N_ID", entity.N_ID, "int");
            parms.Add(":N_PROJECT_ID", entity.N_PROJECT_ID, "int");
            parms.Add(":C_PROJECT_NAME", entity.C_PROJECT_NAME, "string");
            parms.Add(":N_PARENT_PROJECT_ID", entity.N_PARENT_PROJECT_ID, "int");
            parms.Add(":C_PARENT_PROJECT_NAME", entity.C_PARENT_PROJECT_NAME, "string");
            parms.Add(":N_TYPE", entity.N_TYPE, "int");
            parms.Add(":N_MONEY", entity.N_MONEY, "decimal");
            parms.Add(":N_APPLY_ID", entity.N_APPLY_ID, "int");
            parms.Add(":N_USER_ID", entity.N_USER_ID, "int");
            parms.Add(":C_REMARK", entity.C_REMARK, "string");
            parms.Add(":N_APPLY_TYPE", entity.N_APPLY_TYPE, "int");
            var count = NoQuery(sql, parms);
            return count > 0 ? true : false;
        }

        public bool Update(QS_PLANMonth_DEPARTMENT entity)
        {
            var sql = @"UPDATE QS_PLANMonth_DEPARTMENT
SET
	N_PROJECT_ID = :N_PROJECT_ID,
	C_PROJECT_NAME = :C_PROJECT_NAME,
	N_PARENT_PROJECT_ID = :N_PARENT_PROJECT_ID,
	C_PARENT_PROJECT_NAME = :C_PARENT_PROJECT_NAME,
	N_TYPE = :N_TYPE,
	N_MONEY = :N_MONEY,
	N_APPLY_ID = :N_APPLY_ID,
	N_USER_ID = :N_USER_ID,
    C_REMARK = :C_REMARK,
    N_APPLY_TYPE = :N_APPLY_TYPE
WHERE
	N_ID = :N_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":N_ID", entity.N_ID, "int");
            parms.Add(":N_PROJECT_ID", entity.N_PROJECT_ID, "int");
            parms.Add(":C_PROJECT_NAME", entity.C_PROJECT_NAME, "string");
            parms.Add(":N_PARENT_PROJECT_ID", entity.N_PARENT_PROJECT_ID, "int");
            parms.Add(":C_PARENT_PROJECT_NAME", entity.C_PARENT_PROJECT_NAME, "string");
            parms.Add(":N_TYPE", entity.N_TYPE, "int");
            parms.Add(":N_MONEY", entity.N_MONEY, "decimal");
            parms.Add(":N_APPLY_ID", entity.N_APPLY_ID, "int");
            parms.Add(":N_USER_ID", entity.N_USER_ID, "int");
            parms.Add(":C_REMARK", entity.C_REMARK, "string");
            parms.Add(":N_APPLY_TYPE", entity.N_APPLY_TYPE, "int");
            var count = NoQuery(sql, parms);
            return count > 0 ? true : false;
        }

        public bool Delete(QS_PLANMonth_DEPARTMENT entity)
        {
            var sql = "DELETE FROM QS_PLANMonth_DEPARTMENT WHERE N_ID = :N_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":N_ID", entity.N_ID, "int");
            return NoQuery(sql) > 0 ? true : false;
        }

        #endregion 实体操作

        public List<QS_PLANMonth_DEPARTMENT> Exites(int userId, int applyId)
        {
            var moduls = new List<QS_PLANMonth_DEPARTMENT>();
            var sql = @"SELECT T1.*
                        FROM QS_PLANMONTH_DEPARTMENT T1
                        WHERE
                            T1.N_APPLY_ID = :APPLY_ID AND
                            T1.N_USER_ID = :USER_ID
                        ORDER BY N_ID ASC";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APPLY_ID", applyId, "int");
            parms.Add(":USER_ID", userId, "int");
            var dt = Query(sql, parms);
            if (dt.Rows.Count == 0)
            {
                //查询资金申请计划Project
                //查询Proejct信息表
                sql = @"SELECT
                            T1.N_PROID AS PROJECT_ID,
                            T1.C_PRONAME AS PROJECT_NAME,
                            T1.N_TYPE AS TYPE,
                            T1.N_TYPE2 AS TYPE2,
                            nvl(T1.N_NUM1,0) AS ORDER_NUMBER1,
                            nvl(T1.N_NUM2,0) AS ORDER_NUMBER2,
                            T2.N_PROID AS PROJECT_PARENT_ID,
                            nvl(T2.C_PRONAME,0) AS PROJECT_PARENT_NAME
                        FROM QS_PLANMONTH_DETAIL T1
                        LEFT JOIN QS_PLANMONTH_DETAIL T2 ON T1.N_PARENTID = T2.N_PROID AND T1.N_MONTHAPP_ID = T2.N_MONTHAPP_ID
                        WHERE
                            T1.N_MONTHAPP_ID = :APPLY_ID ORDER BY T1.N_MDETAIL_ID ASC";
                parms = new List<Tuple<string, string, string>>();
                parms.Add(":APPLY_ID", applyId, "int");

                foreach (var entity in Query(sql, parms).AsEnumerable())
                {
                    var item = new QS_PLANMonth_DEPARTMENT();
                    moduls.Add(item);
                    item.N_PROJECT_ID = entity.GetValue<int>("PROJECT_ID");
                    item.C_PROJECT_NAME = entity.GetValue<string>("PROJECT_NAME");

                    if (entity.GetValue<int?>("PROJECT_PARENT_ID") == 0)
                    {
                        item.N_PARENT_PROJECT_ID = null;
                        item.C_PARENT_PROJECT_NAME = "";
                    }
                    else
                    {
                        item.N_PARENT_PROJECT_ID = entity.GetValue<int?>("PROJECT_PARENT_ID");
                        item.C_PARENT_PROJECT_NAME = entity.GetValue<string>("PROJECT_PARENT_NAME"); ;
                    }
                    item.N_TYPE = entity.GetValue<int>("TYPE");
                    if (item.N_TYPE == 2)
                    {
                        item.N_MONEY = null;
                    }
                    else
                    {
                        item.N_MONEY = 0;
                    }
                    item.N_USER_ID = userId;
                    item.N_APPLY_ID = applyId;
                    item.N_APPLY_TYPE = entity.GetValue<int>("TYPE2");
                    Insert(item);
                }
            }
            else
            {
                moduls = ConvertToEntity(dt);
            }
            return moduls;
        }

        public JArray ExitesToJarray(int userId, int applyId)
        {
            var moduls = new List<QS_PLANMonth_DEPARTMENT>();
            var sql = @"SELECT
                            T1.*,T2.C_DETAILS_CODE,NULL AS C_IMAGE_URL
                        FROM QS_PLANMONTH_DEPARTMENT T1,QS_PROJECT T2
                        WHERE
                            T1.N_PROJECT_ID = T2.N_PROID AND
                            T1.N_APPLY_ID = :APPLY_ID AND
                            T1.N_USER_ID = :USER_ID
                        ORDER BY N_ID ASC";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APPLY_ID", applyId, "int");
            parms.Add(":USER_ID", userId, "int");
            var dt = Query(sql, parms);
            if (dt.Rows.Count == 0)
            {
                //查询资金申请计划Project
                //查询Proejct信息表
                sql = @"SELECT
                            T1.N_PROID AS PROJECT_ID,
                            T1.C_PRONAME AS PROJECT_NAME,
                            T1.N_TYPE AS TYPE,
                            T1.N_TYPE2 AS TYPE2,
                            nvl(T1.N_NUM1,0) AS ORDER_NUMBER1,
                            nvl(T1.N_NUM2,0) AS ORDER_NUMBER2,
                            T2.N_PROID AS PROJECT_PARENT_ID,
                            nvl(T2.C_PRONAME,0) AS PROJECT_PARENT_NAME
                        FROM QS_PLANMONTH_DETAIL T1
                        LEFT JOIN QS_PLANMONTH_DETAIL T2 ON T1.N_PARENTID = T2.N_PROID AND T1.N_MONTHAPP_ID = T2.N_MONTHAPP_ID
                        WHERE
                            T1.N_MONTHAPP_ID = :APPLY_ID ORDER BY T1.N_MDETAIL_ID ASC";
                parms = new List<Tuple<string, string, string>>();
                parms.Add(":APPLY_ID", applyId, "int");

                foreach (var entity in Query(sql, parms).AsEnumerable())
                {
                    var item = new QS_PLANMonth_DEPARTMENT();
                    moduls.Add(item);
                    item.N_PROJECT_ID = entity.GetValue<int>("PROJECT_ID");
                    item.C_PROJECT_NAME = entity.GetValue<string>("PROJECT_NAME");

                    if (entity.GetValue<int?>("PROJECT_PARENT_ID") == 0)
                    {
                        item.N_PARENT_PROJECT_ID = null;
                        item.C_PARENT_PROJECT_NAME = "";
                    }
                    else
                    {
                        item.N_PARENT_PROJECT_ID = entity.GetValue<int?>("PROJECT_PARENT_ID");
                        item.C_PARENT_PROJECT_NAME = entity.GetValue<string>("PROJECT_PARENT_NAME"); ;
                    }
                    item.N_TYPE = entity.GetValue<int>("TYPE");
                    if (item.N_TYPE == 2)
                    {
                        item.N_MONEY = null;
                    }
                    else
                    {
                        item.N_MONEY = 0;
                    }
                    item.N_USER_ID = userId;
                    item.N_APPLY_ID = applyId;
                    item.N_APPLY_TYPE = entity.GetValue<int>("TYPE2");
                    Insert(item);
                }
            }

            sql = @"SELECT
                            T1.*,T2.C_DETAILS_CODE,NULL AS C_IMAGE_URL
                        FROM QS_PLANMONTH_DEPARTMENT T1,QS_PROJECT T2
                        WHERE
                            T1.N_PROJECT_ID = T2.N_PROID AND
                            T1.N_APPLY_ID = :APPLY_ID AND
                            T1.N_USER_ID = :USER_ID
                        ORDER BY N_ID ASC";
            var parms1 = new List<Tuple<string, string, string>>();
            parms1.Add(":APPLY_ID", applyId, "int");
            parms1.Add(":USER_ID", userId, "int");
            return JArray.FromObject(Query(sql, parms1));
        }

        /// <summary>
        /// 通过资金申请Id获取Load 数据
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns></returns>
        public JArray GetHz(int applyId)
        {
            var sql = @"SELECT
                            T1.N_PROID AS N_PROJECT_ID,
                            T1.C_PRONAME AS C_PROJECT_NAME,
                            T1.N_PARENTID AS N_PARENT_PROJECT_ID,
                            T1.C_PRONAME AS C_PARENT_PROJECT_NAME,
                            T1.N_TYPE AS N_TYPE,
                            CASE T1.N_TYPE
                                WHEN 2 THEN NULL
                                ELSE T1.N_AMOUNT
                            END AS N_MONEY,
                            T1.C_DETAILS_CODE AS C_DETAILS_CODE
                        FROM QS_PLANMONTH_DETAIL T1
                        WHERE
                            T1.N_MONTHAPP_ID = :APPLY_ID";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":APPLY_ID", applyId, "int");
            var arry = new JArray();
            arry = JArray.FromObject(Query(sql, parms));
            //查询所有部门人员填写的申请信息。
            var data = GetDataAll(applyId);
            //查询所有用户信息
            var useBll = new Sys_UserBLL(Service);
            var users = useBll.GetAllUsers();
            //进行分组添加数据
            foreach (var item in data.AsEnumerable().GroupBy(a => a.N_PROJECT_ID))
            {
                var jObject = arry.FirstOrDefault(a => a["N_PROJECT_ID"].ToObject<int>() == item.Key);
                jObject["N_MONEY"] = item.Where(a => a.N_MONEY != null).ToList().Sum(a => a.N_MONEY);

                if (jObject["N_PARENT_PROJECT_ID"].ToString() == "0")
                {
                    jObject["N_PARENT_PROJECT_ID"] = null;
                }

                //抓取所有备注
                var str = "";
                foreach (var item1 in item)
                {
                    if (item1.C_REMARK == null || item1.C_REMARK.Trim() == "")
                    {
                        continue;
                    }
                    if (str != "" && item1.C_REMARK.Substring(item1.C_REMARK.Length - 1, 1) != ",")
                    {
                        str += ",";
                    }
                    Sys_User user = users.FirstOrDefault(u => u.USER_ID == item1.N_USER_ID);
                    str += user.USER_NAME + ":" + item1.C_REMARK;
                }
                jObject["C_REMARK"] = str;

                //添加部门资金详情

                #region 查询部门资金详情去重,并添加到部门汇总资金详情

                QS_PLAN_DETAIL1_BLL qs_detail1 = new QS_PLAN_DETAIL1_BLL();
                QS_PLAN_DETAIL2_BLL qs_detail2 = new QS_PLAN_DETAIL2_BLL();
                QS_PLAN_DETAIL3_BLL qs_detail3 = new QS_PLAN_DETAIL3_BLL();

                string code = jObject["C_DETAILS_CODE"].ToObject<string>();
                if (!string.IsNullOrEmpty(code))
                {
                    switch (code)
                    {
                        case "T1":
                            qs_detail1.GetMonthCollect(applyId, jObject["N_PROJECT_ID"].ToObject<int>());
                            break;

                        case "T2":
                            qs_detail2.GetMonthCollect(applyId, jObject["N_PROJECT_ID"].ToObject<int>());
                            break;

                        default:
                            qs_detail3.GetMonthCollect(applyId, jObject["N_PROJECT_ID"].ToObject<int>());
                            break;
                    }
                }

                #endregion 查询部门资金详情去重,并添加到部门汇总资金详情
            }
            return arry;
        }

        public void Update(int userId, int applyId, JArray arry)
        {
            try
            {
                var sqlList = new List<Tuple<string, List<Tuple<string, string, string>>>>();
                foreach (var item in arry)
                {
                    var sql = @"UPDATE QS_PLANMONTH_DEPARTMENT
                                SET
                                    N_MONEY = :N_MONEY,
                                    C_REMARK = :C_REMARK
                                WHERE
                                    N_PROJECT_ID =:PROJECT_ID AND
                                    N_APPLY_ID = :APPLY_ID AND
                                    N_USER_ID = :USER_ID";
                    var parms = new List<Tuple<string, string, string>>();
                    parms.Add(":APPLY_ID", applyId, "int");
                    parms.Add(":PROJECT_ID", item["N_PROJECT_ID"].ToObject<int>(), "int");
                    parms.Add(":N_MONEY", item["N_MONEY"].ToObject<decimal?>(), "decimal");
                    parms.Add(":C_REMARK", item["C_REMARK"].ToObject<string>(), "string");
                    parms.Add(":USER_ID", userId, "int");
                    sqlList.Add(new Tuple<string, List<Tuple<string, string, string>>>(sql, parms));
                }
                var count = SQLSExcute(sqlList);
            }
            catch (Exception ex)
            {
                throw new Exception("更新部门资金计划编制信息异常");
            }
        }

        public List<QS_PLANMonth_DEPARTMENT> ConvertToEntity(System.Data.DataTable dt)
        {
            var entitys = new List<QS_PLANMonth_DEPARTMENT>();
            foreach (var item in dt.AsEnumerable())
            {
                var entity = new QS_PLANMonth_DEPARTMENT();
                entitys.Add(entity);
                entity.N_ID = item.GetValue<int>("N_ID");
                entity.N_PROJECT_ID = item.GetValue<int>("N_PROJECT_ID");
                entity.C_PROJECT_NAME = item.GetValue<string>("C_PROJECT_NAME");
                entity.N_PARENT_PROJECT_ID = item.GetValue<int?>("N_PARENT_PROJECT_ID");
                entity.C_PARENT_PROJECT_NAME = item.GetValue<string>("C_PARENT_PROJECT_NAME");
                entity.N_TYPE = item.GetValue<int>("N_TYPE");
                entity.N_MONEY = item.GetValue<decimal?>("N_MONEY");
                entity.N_APPLY_ID = item.GetValue<int>("N_APPLY_ID");
                entity.N_USER_ID = item.GetValue<int>("N_USER_ID");
                entity.C_REMARK = item.GetValue<string>("C_REMARK");
                entity.N_APPLY_TYPE = item.GetValue<int>("N_APPLY_TYPE");
            }
            return entitys;
        }

        public List<QS_PLANMonth_DEPARTMENT> GetDataAll(int applyId)
        {
            var sql = "SELECT * FROM QS_PLANMonth_DEPARTMENT T1 WHERE T1.N_APPLY_ID = " + applyId;
            var dt = Query(sql);
            return ConvertToEntity(dt);
        }

        /// <summary>
        /// 通过申请Id 查询 申请人员填写的内容
        /// </summary>
        /// <param name="applyId"></param>
        /// <returns>JArry
        ///  Int UserId 用户Id
        ///  String UserName 用户名
        ///  Int DepartmentId 部门Id
        ///  String DepartmentName 部门名
        ///  Decimal InMoney 总金额（流入）
        ///  Decimal OutMoney 总金额（流出）
        /// </returns>
        public JArray GetDataByApplyId(int applyId)
        {
            var arry = new JArray();
            var data = GetDataAll(applyId);
            var userBll = new Sys_UserBLL(Service);
            var departmentBll = new Sys_DepartmentBLL();
            foreach (var item in data.GroupBy(a => a.N_USER_ID))
            {
                var obj = new JObject();
                arry.Add(obj);

                obj["UserId"] = item.Key;
                var user = userBll.Find(item.Key);
                obj["UserName"] = user == null ? "" : user.USER_NAME;
                Sys_Department department = null;
                if (user != null)
                {
                    department = departmentBll.GetInfoById(user.USER_DEPARTMENT.ToString());
                }
                obj["DepartmentId"] = null;
                if (department != null)
                {
                    obj["DepartmentId"] = department.DEPTID;
                }
                obj["DepartmentName"] = department == null ? "" : department.DEPTNAME;
                obj["InMoney"] = item.Where(a => a.N_APPLY_TYPE == 1 && a.N_TYPE == 2 && a.N_MONEY != null).Sum(a => a.N_MONEY);
                obj["OutMoney"] = item.Where(a => a.N_APPLY_TYPE == 2 && a.N_TYPE == 2 && a.N_MONEY != null).Sum(a => a.N_MONEY);
            }
            return arry;
        }
    }
}